package com.deng.permission.dao.impl;


import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;


import com.deng.common.SQLHelper;
import com.deng.common.vo.OrganizationVo;
import com.deng.permission.dao.ILogin;
import com.deng.permission.vo.UserVo;
import com.deng.util.DataFormater;


public class LoginImpl extends SQLHelper implements ILogin{


	public UserVo checkUser(String userName)
	{
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from t_sys_users " +
					"where code = '"+userName+"' ");
			if(rs.next()){
				UserVo vo = new UserVo();
				vo.userName = rs.getString("code");
				vo.name = rs.getString("name");
				vo.password = rs.getString("password");
				vo.is_valid = rs.getInt("is_valid");
				vo.admin = rs.getInt("admin");

				
				
				vo.pf_gongzuojixiao = rs.getString("pf_gongzuojixiao");
				vo.pf_jingyingjixiao = rs.getString("pf_jingyingjixiao");
				vo.pf_xingzhengkeshi = rs.getString("pf_xingzhengkeshi");
				vo.pf_kpi = rs.getString("pf_kpi");
				vo.pf_gongzuojixiaogeren = rs.getString("pf_gongzuojixiaogeren");
				vo.pf_paiban = rs.getString("pf_paiban");
				vo.sh_gongzuojixiao = rs.getString("sh_gongzuojixiao");
				vo.sh_jingyingjixiao = rs.getString("sh_jingyingjixiao");
				vo.sh_xingzhengkeshi = rs.getString("sh_xingzhengkeshi");
				vo.sh_kpi = rs.getString("sh_kpi");
				vo.sh_gongzuojixiaogeren = rs.getString("sh_gongzuojixiaogeren");
				vo.sh_paiban = rs.getString("sh_paiban");
				vo.jjsh_gongzuojixiao = rs.getString("jjsh_gongzuojixiao");
				vo.erci_gongzuojixiao = rs.getString("erci_gongzuojixiao");
				vo.jjsh_gongzuohebing = rs.getString("jjsh_gongzuohebing");
				vo.jjsh_jingyingjixiao = rs.getString("jjsh_jingyingjixiao");
				vo.erci_jingyingjixiao = rs.getString("erci_jingyingjixiao");
				vo.jjsh_xingzhengkeshi = rs.getString("jjsh_xingzhengkeshi");
				vo.gongshi = rs.getInt("gongshi");
				vo.jichu = rs.getInt("jichu");
				vo.shujuguanli = rs.getInt("shujuguanli");
				vo.etl_shenhe = rs.getInt("etl_shenhe");
				vo.quanxian = rs.getInt("quanxian");
				vo.baobiao = rs.getInt("baobiao");
				vo.pf_jiangcheng = rs.getInt("pf_jiangcheng");
				vo.pf_lingdao = rs.getInt("pf_lingdao");
//				vo.guishu_id = rs.getString("guishu_id");
				vo.guishu_id = getGuishuId(vo.userName,conn);
				vo.load_kpi = rs.getString("load_kpi");
				vo.return_action = rs.getInt("return_action");
//				vo.ycfp_manager = rs.getString("ycfp_manager");
//				vo.ecfp_manager = rs.getString("ecfp_manager");
//				vo.yccx = rs.getString("yccx");
//				vo.eccx = rs.getString("eccx");
//				vo.base_parameter = rs.getString("base_parameter");
//				vo.admin = rs.getString("admin");
//				if(vo.userName == "admin"){
//					vo.role = 1;
//				}else{
//					vo.role = 2;
//				}
				freeConnection(conn);
				return vo;
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	public UserVo checkUser(String userName, String password) {
		// TODO Auto-generated method stub
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from t_sys_users " +
					"where code = '"+userName+"' and password = '"+password+"'");
			if(rs.next()){
				UserVo vo = new UserVo();
				vo.userName = rs.getString("code");
				vo.name = rs.getString("name");
				vo.password = rs.getString("password");
				vo.is_valid = rs.getInt("is_valid");
				vo.admin = rs.getInt("admin");

				
				
				vo.pf_gongzuojixiao = rs.getString("pf_gongzuojixiao");
				vo.pf_jingyingjixiao = rs.getString("pf_jingyingjixiao");
				vo.pf_xingzhengkeshi = rs.getString("pf_xingzhengkeshi");
				vo.pf_kpi = rs.getString("pf_kpi");
				vo.pf_gongzuojixiaogeren = rs.getString("pf_gongzuojixiaogeren");
				vo.pf_paiban = rs.getString("pf_paiban");
				vo.sh_gongzuojixiao = rs.getString("sh_gongzuojixiao");
				vo.sh_jingyingjixiao = rs.getString("sh_jingyingjixiao");
				vo.sh_xingzhengkeshi = rs.getString("sh_xingzhengkeshi");
				vo.sh_kpi = rs.getString("sh_kpi");
				vo.sh_gongzuojixiaogeren = rs.getString("sh_gongzuojixiaogeren");
				vo.sh_paiban = rs.getString("sh_paiban");
				vo.jjsh_gongzuojixiao = rs.getString("jjsh_gongzuojixiao");
				vo.erci_gongzuojixiao = rs.getString("erci_gongzuojixiao");
				vo.jjsh_gongzuohebing = rs.getString("jjsh_gongzuohebing");
				vo.jjsh_jingyingjixiao = rs.getString("jjsh_jingyingjixiao");
				vo.erci_jingyingjixiao = rs.getString("erci_jingyingjixiao");
				vo.jjsh_xingzhengkeshi = rs.getString("jjsh_xingzhengkeshi");
				vo.gongshi = rs.getInt("gongshi");
				vo.jichu = rs.getInt("jichu");
				vo.shujuguanli = rs.getInt("shujuguanli");
				vo.etl_shenhe = rs.getInt("etl_shenhe");
				vo.quanxian = rs.getInt("quanxian");
				vo.baobiao = rs.getInt("baobiao");
				vo.pf_jiangcheng = rs.getInt("pf_jiangcheng");
				vo.pf_lingdao = rs.getInt("pf_lingdao");
//				vo.guishu_id = rs.getString("guishu_id");
				vo.guishu_id = getGuishuId(vo.userName,conn);
				vo.load_kpi = rs.getString("load_kpi");
				vo.return_action = rs.getInt("return_action");
//				vo.ycfp_manager = rs.getString("ycfp_manager");
//				vo.ecfp_manager = rs.getString("ecfp_manager");
//				vo.yccx = rs.getString("yccx");
//				vo.eccx = rs.getString("eccx");
//				vo.base_parameter = rs.getString("base_parameter");
//				vo.admin = rs.getString("admin");
//				if(vo.userName == "admin"){
//					vo.role = 1;
//				}else{
//					vo.role = 2;
//				}
				freeConnection(conn);
				return vo;
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	public List<OrganizationVo> getGuishuOrganizationList(String code){
		Connection conn = getConnect();
		try{
			List<OrganizationVo> lst = new ArrayList<OrganizationVo>();
			OrganizationVo o;
			String sql = "select duty.name+'-'+dpt.name as fName," +
					"	dpt.id as oid" +
					"	from dbo.t_sys_users_vs_department as vs" +
					"	left join dbo.t_sys_stat_department  as dpt" +
					"	on dpt.id = vs.organization_id" +
					"	left join dbo.DICT_DUTY_TYPE as duty" +
					"	on dpt.duty_id = duty.id" +
					"	where code = '"+code+"'";
			ResultSet rs = queryData(conn, sql);
			while(rs.next()){
				o = new OrganizationVo();
				o.id = rs.getInt("oid");
				o.full_name = rs.getString("fName");
				lst.add(o);
			}
			return lst;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	private String getGuishuId(String code,Connection conn)
	{
		if(conn == null){
			conn = getConnect();
		}
		String rt = "";
		try
		{
			ResultSet rs = queryData(conn,"select * from t_sys_users_vs_department " +
					"where code = '"+code+"'");
			
			while(rs.next()){
				int orgId = rs.getInt("organization_id");
				rt += orgId+":";
			}
			if(rt.length() != 0 )
			{
				return rt;
			}
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
			
		}
		return null;
	}
	public int changePW(UserVo vo,String oldPW, String newPW) {
		Connection conn = getConnect();
		try{
			ResultSet rs = queryData(conn,"select * from t_sys_users " +
					"where code = '"+vo.userName+"' and password = '"+oldPW+"'");
			if(rs.next()){
				int x = updateData(conn,"update t_sys_users set password = '"+newPW+"' "+
						"where code = '"+vo.userName+"' and password = '"+oldPW+"'");
				freeConnection(conn);
				return x;
			}
		}catch(Exception ex){
			freeConnection(conn);
			ex.printStackTrace();
		}
		freeConnection(conn);
		return 0;
	}

	public List<UserVo> getAllUser() {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from t_sys_users  order by code");
			List<UserVo> l = new ArrayList<UserVo>();
			while(rs.next()){
				UserVo vo = new UserVo();
				
				vo.userName = rs.getString("code");
				vo.name = rs.getString("name");
				vo.password = rs.getString("password");
				vo.is_valid = rs.getInt("is_valid");
				vo.admin = rs.getInt("admin");

				
				
				vo.pf_gongzuojixiao = rs.getString("pf_gongzuojixiao");
				vo.pf_jingyingjixiao = rs.getString("pf_jingyingjixiao");
				vo.pf_xingzhengkeshi = rs.getString("pf_xingzhengkeshi");
				vo.pf_kpi = rs.getString("pf_kpi");
				vo.pf_gongzuojixiaogeren = rs.getString("pf_gongzuojixiaogeren");
				vo.pf_paiban = rs.getString("pf_paiban");
				vo.sh_gongzuojixiao = rs.getString("sh_gongzuojixiao");
				vo.sh_jingyingjixiao = rs.getString("sh_jingyingjixiao");
				vo.sh_xingzhengkeshi = rs.getString("sh_xingzhengkeshi");
				vo.sh_kpi = rs.getString("sh_kpi");
				vo.sh_gongzuojixiaogeren = rs.getString("sh_gongzuojixiaogeren");
				vo.sh_paiban = rs.getString("sh_paiban");
				vo.jjsh_gongzuojixiao = rs.getString("jjsh_gongzuojixiao");
				vo.erci_gongzuojixiao = rs.getString("erci_gongzuojixiao");
				vo.jjsh_gongzuohebing = rs.getString("jjsh_gongzuohebing");
				vo.jjsh_jingyingjixiao = rs.getString("jjsh_jingyingjixiao");
				vo.erci_jingyingjixiao = rs.getString("erci_jingyingjixiao");
				vo.jjsh_xingzhengkeshi = rs.getString("jjsh_xingzhengkeshi");
				vo.gongshi = rs.getInt("gongshi");
				vo.jichu = rs.getInt("jichu");
				vo.shujuguanli = rs.getInt("shujuguanli");
				vo.etl_shenhe = rs.getInt("etl_shenhe");
				vo.quanxian = rs.getInt("quanxian");
				vo.baobiao = rs.getInt("baobiao");
				vo.pf_jiangcheng = rs.getInt("pf_jiangcheng");
				vo.pf_lingdao = rs.getInt("pf_lingdao");
				vo.userName = rs.getString("code");
				vo.name = rs.getString("name");
				vo.password = rs.getString("password");
				vo.load_kpi = rs.getString("load_kpi");
//				vo.guishu_id = rs.getString("guishu_id");
				vo.guishu_id = getGuishuId(vo.userName,conn);
				vo.return_action = rs.getInt("return_action");
//				vo.ycfp_manager = rs.getString("ycfp_manager");
//				vo.ecfp_manager = rs.getString("ecfp_manager");
//				vo.yccx = rs.getString("yccx");
//				vo.eccx = rs.getString("eccx");
//				vo.base_parameter = rs.getString("base_parameter");
//				vo.admin = rs.getString("admin");
				l.add(vo);
			}
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public int addUser(UserVo vo) {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from t_sys_users where code = '"+vo.userName+"'");
			if(rs.next()){
				return 0;
			}
			
			StringBuffer sb = new StringBuffer();
			sb.append("insert into t_sys_users " +
					"	(code,password,name,is_valid,admin)" +
					"	values( '"+vo.userName+"','"+vo.password+"','"+vo.name+"',1,"+vo.admin
//					+","+DataFormater.sqlString(vo.guishu_id)
					+")");
			
			int x = updateData(conn,sb.toString());
			replaceUserOrg(vo.userName,vo.guishu_id,conn);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public void updateUser(UserVo vo) {
		Connection conn = getConnect();
		try{
			conn.setAutoCommit(false);
			
			StringBuffer sb = new StringBuffer();
			sb.append("update t_sys_users set password = '"+vo.password+"' ,admin="+vo.admin+",name = "+DataFormater.sqlString(vo.name)+
						"  where code = '"+vo.userName+"'");
			updateData(conn,sb.toString());	
			replaceUserOrg(vo.userName,vo.guishu_id,conn);
			
			conn.commit();
			
		}catch(Exception ex){
			ex.printStackTrace();
			try{
				if(conn != null){
					conn.rollback();//回滚。
				}
			}catch(Exception ex2){
				ex2.printStackTrace();
			}
		}finally{
			try{
				if(conn != null){
					conn.setAutoCommit(true);
				}
			}catch(Exception ex2){
				ex2.printStackTrace();
			}
		}
	}
	private void replaceUserOrg(String code,String guishuIds,Connection conn)
	{
		try
		{
			String sql = "delete t_sys_users_vs_department where code = '"+code+"'";
			updateData(conn, sql);
			if(guishuIds == null || guishuIds.equals(""))
			{
				return;
			}
			String[] guishuArr = guishuIds.split(":");
			for(int i = 0;i<guishuArr.length;i++)
			{
				sql = "insert into t_sys_users_vs_department(code,organization_id) values('"+code+"',"+guishuArr[i]+")";
				updateData(conn, sql);
			}
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	public int addRule(UserVo vo, String type, int id) {
		try{
			String sql = "";
			System.out.println("type = "+type);
			if(type.equals("pf_gongzuojixiao") ||type.equals("pf_kpi")
				||type.equals("sh_gongzuojixiao")||type.equals("sh_kpi")
				||type.equals("jjsh_gongzuojixiao")||type.equals("erci_gongzuojixiao")
				||type.equals("pf_jingyingjixiao")||type.equals("sh_jingyingjixiao")
				||type.equals("jjsh_jingyingjixiao")||type.equals("erci_jingyingjixiao")
				||type.equals("pf_paiban")||type.equals("sh_paiban")
				||type.equals("pf_gongzuojixiaogeren")||type.equals("sh_gongzuojixiaogeren")
				||type.equals("pf_xingzhengkeshi")||type.equals("sh_xingzhengkeshi")
				||type.equals("jjsh_xingzhengkeshi")
				){
				sql = "update t_sys_users " +
						"	set "+type+"  = (case when "+type+" is null then ':"+id+":' else "+type+"+':"+id+":' end)" +
						"	where code = '"+vo.userName+"'";
			}else if(type.equals("gongshi")||type.equals("jichu")
					||type.equals("shujuguanli")||type.equals("etl_shenhe")){
				sql = "update t_sys_users " +
				"	set "+type+"  = "+id+" where code = '"+vo.userName+"'";
			}
			Connection conn = getConnect();			
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int deleteRule(UserVo vo, String type, int id) {
		try{
			String sql = "";
			if(type.equals("pf_gongzuojixiao") ||type.equals("pf_kpi")
				||type.equals("sh_gongzuojixiao")||type.equals("sh_kpi")
				||type.equals("jjsh_gongzuojixiao")||type.equals("erci_gongzuojixiao")
				||type.equals("pf_jingyingjixiao")||type.equals("sh_jingyingjixiao")
				||type.equals("jjsh_jingyingjixiao")||type.equals("erci_jingyingjixiao")
				||type.equals("pf_paiban")||type.equals("sh_paiban")
				||type.equals("pf_gongzuojixiaogeren")||type.equals("sh_gongzuojixiaogeren")
				||type.equals("pf_xingzhengkeshi")||type.equals("sh_xingzhengkeshi")
				||type.equals("jjsh_xingzhengkeshi")
				){
				sql = "update t_sys_users " +
						"	set "+type+"  = replace("+type+",':1:','') " +
						"	where code = '"+vo.userName+"'";
			}else if(type.equals("gongshi")||type.equals("jichu")
					||type.equals("shujuguanli")||type.equals("etl_shenhe")){
				sql = "update t_sys_users " +
				"	set "+type+"  = "+id+" where code = '"+vo.userName+"'";
			}
			Connection conn = getConnect();
			
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int functionModifyRole(UserVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_sys_users " +
				"	set gongshi = "+(vo.gongshi)+
				"	where code = '"+vo.userName+"'";
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}
	public int baseDictModifyRole(UserVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_sys_users " +
				"	set jichu = "+(vo.jichu)+
				"	where code = '"+vo.userName+"'";
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int roleModifyRole(UserVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_sys_users " +
				"	set quanxian = "+(vo.quanxian)+
				"	where code = '"+vo.userName+"'";
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int pfkpiModifyRole(UserVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_sys_users " +
				"	set pf_kpi = "+(vo.pf_kpi)+
				"	where code = '"+vo.userName+"'";
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}
	public int pfgongzuoModifyRole(UserVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_sys_users " +
				"	set pf_gongzuojixiao = '"+(vo.pf_gongzuojixiao)+"'"+
				"	where code = '"+vo.userName+"'";
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}
	public int shkpiModifyRole(UserVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_sys_users " +
				"	set sh_kpi = "+(vo.sh_kpi)+
				"	where code = '"+vo.userName+"'";
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}
	public int shgongzuoModifyRole(UserVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_sys_users " +
				"	set sh_gongzuojixiao = "+(vo.sh_gongzuojixiao)+
				"	where code = '"+vo.userName+"'";
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int baobiaoRole(UserVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_sys_users " +
				"	set baobiao = "+(vo.baobiao)+
				"	where code = '"+vo.userName+"'";
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}
	public int lingdaoRole(UserVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_sys_users " +
				"	set pf_lingdao = "+(vo.pf_lingdao)+
				"	where code = '"+vo.userName+"'";
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}
	public int jiangchengRole(UserVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_sys_users " +
				"	set pf_jiangcheng = "+(vo.pf_jiangcheng)+
				"	where code = '"+vo.userName+"'";
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int shYicijiangjinRole(UserVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_sys_users " +
				"	set sh_yicijiangjin = "+(vo.sh_yicijiangjin)+
				"	where code = '"+vo.userName+"'";
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int loadkpiModifyRole(UserVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_sys_users " +
				"	set load_kpi = "+(vo.load_kpi)+
				"	where code = '"+vo.userName+"'";
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	
	}
	public int returnActionModifyRole(UserVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_sys_users " +
				"	set return_action = "+(vo.return_action)+
				"	where code = '"+vo.userName+"'";
			int x = updateData(conn, sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;	
	}
	
}
